package SQL;

import oracle.jdbc.oracore.OracleType;

import java.sql.*;

public class Demo1 {
    public static void main(String[] agrs){
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            System.out.println("驱动加载成功!");//实现接口
            String url="jdbc:oracle:thin:@192.168.1.109:1521:xe";
            String user="hr";
            String pwd="123";
            Connection con= DriverManager.getConnection(url,user,pwd);//Connection 接口连接数据库
            System.out.println("连接成功！");
            Statement stm=con.createStatement();//调用Statement方法
            String sql="Select * from employees";
            ResultSet rs=stm.executeQuery(sql);//查询数据
            while (rs.next()){
                System.out.println(rs.getString(2)+" "+rs.getInt("salary"));
            }
            System.out.println("----------------------------------");
            String sql1="select * from employees where salary>?and department_id=?";
            PreparedStatement pstm=con.prepareStatement(sql1);
            pstm.setInt(1,10000);
            pstm.setInt(2,80);
            ResultSet rs1=pstm.executeQuery();
            while (rs1.next()){
                System.out.println(rs1.getString(2)+" "+rs1.getInt("salary"));
            }
            String sql2="{call avg_salary(?)}";
            CallableStatement cstm=con.prepareCall(sql2);//调用存储函数
            double avg;
            cstm.registerOutParameter(1, OracleType.STYLE_DOUBLE);
            cstm.executeQuery();
            avg=cstm.getDouble(1);
            System.out.println("平均工资："+avg);
            //依次关闭
            rs.close();
            stm.close();
            con.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            System.out.println("驱动加载失败!");
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
